package com.gugu.procedure.dao;

import com.gugu.procedure.bean.OrderItem;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import java.util.List;
import java.util.Map;

public interface OrderItemDao extends JpaRepository <OrderItem, Long>, JpaSpecificationExecutor <OrderItem> {

    List <OrderItem> findByOrderNo(String orderNo);

    OrderItem findTopByOrderNoAndSpecs(String orderNo, String specs);

    OrderItem findTopByOrderNoAndSeque(String orderNo, String seque);


    @Query(value = "\tselect  *  from  order_item  where order_no=?1 and  convert( REGEXP_REPLACE(number_start,'[a-zA-Z]',''),SIGNED)<= " +
            "convert( REGEXP_REPLACE(?2,'[a-zA-Z]','') ,SIGNED)  and  convert( REGEXP_REPLACE(?2,'[a-zA-Z]','') ,SIGNED) " +
            "<= convert( REGEXP_REPLACE(number_end,'[a-zA-Z]',''),SIGNED)  limit 0,1\n", nativeQuery = true)
    OrderItem queryByOrderNoAndNumberForUpdate(String orderNo, String number);


    @Query(value = "\tselect  *  from  order_item  where order_no=?1 and  number_start<= ?2  and  ?2 <= number_end  limit 0,1\n", nativeQuery = true)
    OrderItem queryByOrderNoAndNumber1(String orderNo, String number);

    List <OrderItem> findByOrderNoAndNumberStartIsLessThanEqualAndNumberEndIsGreaterThanEqual(String orderNo, String startNumber, String endNumber);

    @Query(value = "select  * from order_item where order_no=?1 and  specs in(" +
            "select  specs from production where order_no=?1 and procedures like ?2)", nativeQuery = true)
    List <OrderItem> listOrderItemByOrderNoAndProduction(String orderNo, String production);


    void deleteAllByOrderNo(String orderNo);

    @Query(value =
            "select \n " +
                    "b.tolerance_up,\n" +
                    "b.tolerance_down,\n" +
                    "b.shell_size,\n" +
                    "b.rough_supplier,\n" +
                    "b.rough_item_no,\n" +
                    "b.compress_up,\n" +
                    "b.compress_down,\n" +
                    "b.dingjing_up,\n" +
                    "b.dingjing_down,\n" +
                    "b.order_no,\n" +
                    "c.laser_param_angle,\n" +
                    "c.laser_param_up1,\n" +
                    "c.laser_param_up2,\n" +
                    "c.laser_param_down1,\n" +
                    "c.laser_param_down2\n" +
                    "from  order_item  b, parameter c\n" +
                    "where c.order_no=b.order_no and c.specs=b.specs\n" +
                    " and b.order_no=?1 and b.specs=?2", nativeQuery = true)
    Map <String, Object> queryReplenishmentParams(String orderNo, String specs);

}
